import pandas as pd
import cx_Oracle
import xlrd
import json
import datetime

addday = 15*30
insertSql = "Insert Into CSC_GOODS_DELIVERY_RECORD(ID,CREATE_BY,CREATE_DATE,SYS_COMPANY_CODE,IMEI,IMEI2,SN,MATERIAL_CODE,MODEL,MANUFACTURE_DATE,WARRANTY_START_DATE,WARRANTY_END_DATE,REGISTRATION_DATE) Values (sys_guid(),'AdminImport2',sysdate,'HY1',:imei,:imei2,:sn,:mtlCode,:model,:ccdate,:startdate,:enddate,:jhdate)"


type_data = pd.read_excel(r"C:\Users\tanxiong\Desktop\20200929SN\KEY.xlsx")
type_data['newkey'] = type_data['机型']+type_data['颜色']
dict_country = type_data.set_index('newkey').T.to_dict('dict')
def getTypeDate(key):
    try:
        return dict_country[key]["IOT编码"]
    except Exception as e:
        print(e)
        return ""

def importData(importPath,dbconnect):
    dfdata = pd.read_csv(importPath,encoding="UTF-8",low_memory=False)
    dfdata = dfdata.fillna("1900-01-01 00:00:00")
    datas = []
    total = len(dfdata)
    for index,rowa in dfdata.iterrows():
          
        imei = rowa["IMEI"]
        model = rowa["机型"].strip()
        core = rowa["颜色"].strip()
        ckdate = rowa["出库时间"]
        jhdate = rowa["激活时间"]
        suk = getTypeDate(model+core)
        localTime = datetime.datetime.strptime(ckdate,'%Y-%m-%d %H:%M:%S')
        endTime = localTime+datetime.timedelta(days=addday)
        if jhdate != '' and jhdate != 'NaN':
            jhdate = datetime.datetime.strptime(jhdate,'%Y-%m-%d %H:%M:%S')
        else:
            jhdate = None
        datas.append(
            {'imei':imei,'imei2':imei,'sn':imei,'mtlCode':suk,'model':model,'ccdate':localTime,'startdate':localTime,'enddate':endTime,'jhdate':jhdate}
            )

        if index % 10000 == 0 or index == total-1:
             dbconnect.insertRows(insertSql,datas)
             datas.clear()
       

class CscGoods():
    def __init__(self,use,passwd,database):
        connection = cx_Oracle.connect(use,passwd,database,encoding="UTF-8")
        cursor = connection.cursor()
        print('初始化数据连接池成功！')
        self.connection = connection
        self.cursor = cursor
    def disconnect(self):
        self.cursor.close()
        self.connection.close()
    def insertRows(self,sql,dataLines):
        try:
            # print(dataLines)
            self.cursor.executemany(sql,dataLines)
            self.connection.commit()
            print("插入ok")
        except Exception as e:
            print(e)
            self.disconnect()

if __name__ == "__main__":
    # cscgood = CscGoods('CSC','IoTdev1905csc','10.100.21.122:1521/IOT_CSC')
    cscGoods = CscGoods('CSC_IOT','IoTprd1912csc','platformdb02.iotscm.shbt.111.net:1521/IOT_CSC')
    importData(r"C:\Users\tanxiong\Desktop\20200929SN\IMEI-2.csv",cscGoods)
    cscGoods.disconnect()
   

